﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using DevExpress.Xpo;
using System.Reflection;
using System.Linq;
using DevExpress.Data.Filtering;
using DevExpress.Persistent.BaseImpl;
using DevExpress.Xpo.Metadata;
using XpoMembership;

namespace XpoImport
{

    public class ImportBase
    {
        private static string ImportIdField = "OldImportId";

        public event EventHandler ImportStarted;
        public event EventHandler ImportProgress;
        public event EventHandler ImportComplete;

        private List<LookupData> lookupData = new List<LookupData>();

        private float _progress = 0;
        private int _count;
        
        internal List<object> existingList = new List<object>();
        
        internal Dictionary<PropertyInfo, FieldImportAttribute> propertyList = new Dictionary<PropertyInfo, FieldImportAttribute>();

        #region Utility Functions
        private static void SetMemberValue(object item, string name, object value)
        {
            if (item.GetType().BaseType == typeof(XPObject))
                ((XPObject)item).SetMemberValue(name, value);
            else if (item.GetType().BaseType == typeof(XpoUser))
                ((XpoUser)item).SetMemberValue(name, value);
        }

        private static object GetMemberValue(object item, string name)
        {
            if (item.GetType().BaseType == typeof(XPObject))
                return ((XPObject)item).GetMemberValue(name);
            else if (item.GetType().BaseType == typeof(XpoUser))
                return ((XpoUser)item).GetMemberValue(name);

            return null;
        }

        private static object InvokeMethod(object obj, string methodName)
        {
            try
            {
                MethodInfo info = obj.GetType().GetMethod(methodName);

                if (info != null)
                    info.Invoke(obj, new object[] { });
            }
            catch
            {
                throw;
            }

            return null;
        }

        private static DataTable DbQuery(SqlConnection conn, string query)
        {
            try
            {
                using (SqlCommand command = new SqlCommand(query, conn))
                {
                    using (DataSet dataset = new DataSet())
                    {
                        using (SqlDataAdapter adaptor = new SqlDataAdapter(command))
                        {
                            adaptor.Fill(dataset, "Query");
                        }
                        return dataset.Tables[0];
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        #region Conversion Functions
        public double AsDouble(object value)
        {
            try
            {
                return (double)value;
            }
            catch 
            {
                return double.MinValue;
            }
        }

        public bool AsBool(object value)
        {
            try
            {
                return (bool)value;
            }
            catch 
            {
                return false;
            }
        }

        public string AsString(object value)
        {
            try
            {
                return value.ToString();
            }
            catch 
            {
                return string.Empty;
            }
        }

        public int AsInt(object value)
        {
            try
            {
                return (int)value;
            }
            catch 
            {
                return int.MinValue;
            }
        }

        public DateTime? AsDateTime(object value)
        {
            try
            {
                return (DateTime)value;
            }
            catch 
            {
                return null;
            }
        }
        #endregion

        private string _Name;
        public string Name
        {
            get { return _Name; }
            set
            {
                _Name = value;
            }
        }

        private string _SourceTable;
        public string SourceTable
        {
            get { return _SourceTable; }
            set
            {
                _SourceTable = value;
            }
        }

        private string _SelectExpression;
        public string SelectExpression
        {
            get { return _SelectExpression; }
            set
            {
                _SelectExpression = value;
            }
        }
        
        private string _JoinExpression;
        public string JoinExpression
        {
            get { return _JoinExpression; }
            set
            {
                _JoinExpression = value;
            }
        }
        
        private string _KeyField;
        public string KeyField
        {
            get { return _KeyField; }
            set
            {
                _KeyField = value;
            }
        }

        private Type _DestinationType;
        public Type DestinationType
        {
            get { return _DestinationType; }
            set
            {
                _DestinationType = value;
            }
        }

        private int _ImportOrder;
        public int ImportOrder
        {
            get { return _ImportOrder; }
            set
            {
                _ImportOrder = value;
            }
        }

        
        public float Progress
        {
        	get	{ return _progress; }
        }

        public override string ToString()
        {
            return this.DestinationType.Name;
        }

        public ImportBase(Type classType) 
        {
            TableImportAttribute tableImport = null;
            TableImportIndexAttribute tableIndex = null;
            TableJoinAttribute tableJoin = null;

            object[] attributes = classType.GetCustomAttributes(typeof(TableImportAttribute), false);
            if (attributes.Length == 1)
                tableImport = attributes[0] as TableImportAttribute;
            
            attributes = classType.GetCustomAttributes(typeof(TableImportIndexAttribute), false);
            if (attributes.Length == 1)
            {
                tableIndex = attributes[0] as TableImportIndexAttribute;
                _ImportOrder = tableIndex.ImportOrder;
            }

            attributes = classType.GetCustomAttributes(typeof(TableJoinAttribute), false);
            if (attributes.Length == 1)
            {
                tableJoin = attributes[0] as TableJoinAttribute;
                _JoinExpression = tableJoin.JoinExpression;
                _SelectExpression = tableJoin.SelectExpression;
            }

            _Name = classType.Name;
            
            _SourceTable = tableImport.SourceTable;
            _KeyField = tableImport.KeyField;

            _DestinationType = classType;
            //_KeyProperty = tableImport.KeyProperty;
            //_OldIdField = tableImport.OldIdField;
        }


        private object GetLookupValue(Type targetType, object lookupValue)
        {
            var cachedItem = lookupData.Where(item => item.Type == targetType && item.Id.Equals(lookupValue));

            if (cachedItem.Count() == 0)
            {
                var foundItem = XpoSession.FindObject(targetType, new BinaryOperator(ImportIdField, lookupValue));

                if (foundItem != null)
                {
                    LookupData newCacheItem = new LookupData { Type = targetType, Id = lookupValue, Item = foundItem };
                    lookupData.Add(newCacheItem);

                    return foundItem;
                } else {
                    return null;
                }
            }
            else
            {
                return cachedItem.First().Item;
            }
        }

        private object GetPersistentLookupValue(Type targetType, object lookupValue)
        {
            var cachedItem = lookupData.Where(item => item.Type == targetType && item.Id.Equals(lookupValue));

            if (cachedItem.Count() == 0)
            {
                string idField = "";

                if (targetType.Name == "State")
                {
                    idField = "LongName";

                    State foundItem = XpoSession.FindObject<State>(new BinaryOperator(idField, lookupValue));

                    if (foundItem == null)
                    {
                        foundItem = new State(XpoSession);
                        foundItem.LongName = lookupValue.ToString();
                        foundItem.Save();
                    }

                    LookupData newCacheItem = new LookupData();
                    newCacheItem.Type = targetType;
                    newCacheItem.Id = lookupValue;
                    newCacheItem.Item = foundItem;
                    lookupData.Add(newCacheItem);

                    return foundItem;
                }
                else if (String.Compare(targetType.Name, "Country", false) == 0)
                {
                    idField = "Name";

                    Country foundItem = XpoSession.FindObject<Country>(new BinaryOperator(idField, lookupValue));
                    
                    if (foundItem == null)
                    {
                        foundItem = new Country(XpoSession);
                        foundItem.Name = lookupValue.ToString();
                        foundItem.Save();
                    }

                    LookupData newCacheItem = new LookupData();
                    newCacheItem.Type = targetType;
                    newCacheItem.Id = lookupValue;
                    newCacheItem.Item = foundItem;
                    lookupData.Add(newCacheItem);

                    return foundItem;
                }
                else
                {
                    throw new NotImplementedException("GetPersistentLookupValue(" + targetType.Name + ") is not implemented.");
                }
            }
            else
            {
                return cachedItem.First().Item;
            }
        }

        //public abstract bool Exists(DataRow row);
        private bool Import(DataRow row)
        {
            XPClassInfo classInfo = XpoSession.GetClassInfo(this.DestinationType);
            object item = classInfo.CreateNewObject(this.XpoSession);

            foreach (KeyValuePair<PropertyInfo, FieldImportAttribute> property in propertyList)
            {
                string sourceField = property.Value.SourceField;
                if (sourceField == "")
                    sourceField = property.Key.Name;

                object sourceValue = row[sourceField];

                if (sourceValue == DBNull.Value)
                {
                    sourceValue = null;
                }
                else
                {
                    switch (property.Value.ConversionType)
                    {
                        case FieldImportAttribute.FieldConversionType.None:
                            if (sourceValue == DBNull.Value)
                                sourceValue = null;
                            break;
                        case FieldImportAttribute.FieldConversionType.IntegerToString:
                            sourceValue = sourceValue.ToString();
                            break;
                        case FieldImportAttribute.FieldConversionType.StringToPhoneNumber:
                            PhoneNumber phone = new PhoneNumber(this.XpoSession);
                            phone.Number = (string)sourceValue;
                            sourceValue = phone;
                            break;
                        case FieldImportAttribute.FieldConversionType.Lookup:
                            sourceValue = GetLookupValue(property.Key.PropertyType, sourceValue);
                            break;
                        case FieldImportAttribute.FieldConversionType.PersistentLookup:
                            sourceValue = GetPersistentLookupValue(property.Key.PropertyType, sourceValue);
                            break;
                        case FieldImportAttribute.FieldConversionType.Boolean:
                            sourceValue = ((int)sourceValue == -1);
                            break;
                        case FieldImportAttribute.FieldConversionType.Enum:
                            //sourceValue = GetLookupValue(property.Key.PropertyType, sourceValue);
                            //System.Enum.GetValues(property.Key.PropertyType)
                            if (sourceValue is string)
                            {
                                sourceValue = System.Enum.Parse(property.Key.PropertyType, (string)sourceValue, true);
                            }
                            else
                            {
                                throw new Exception("FieldConversionType.Enum Conversion Error (Value is not string).");
                            }
                            break;
                    }
                }

                SetMemberValue(item, property.Key.Name, sourceValue);
            }

            if (KeyField != "")
                SetMemberValue(item, ImportIdField, row[KeyField]);

            InvokeMethod(item, "Save");

            return true;
        }

        //public abstract bool InitializeExisting();

        private Session _xpoSession;
        public Session XpoSession
        {
            get
            {
                return _xpoSession;
            }
        }

        private bool Exists(DataRow row)
        {
            return existingList.Contains(row[KeyField]);
        }

        public void InitializeExisting()
        {
            XPCursor itemCollection = new XPCursor(XpoSession, DestinationType);

            foreach (object item in itemCollection)
            {
                existingList.Add(GetMemberValue(item, ImportIdField));
            }
        }

        SqlConnection _sqlConnection = null;

        public void Execute(SqlConnection sqlConnection, Session xpoSession)
        {
            if (ImportStarted != null) ImportStarted(this, null);

            _sqlConnection = sqlConnection;
            _xpoSession = xpoSession;

            XPClassInfo classInfo = xpoSession.GetClassInfo(DestinationType);

            DataTable table = null;
            if (JoinExpression != null)
            {
                table = DbQuery(_sqlConnection, "SELECT " + SelectExpression + " FROM " + SourceTable + " " + JoinExpression + " WHERE " + SourceTable + "." + KeyField + " IS NOT NULL");
            }
            else
            {
                table = DbQuery(_sqlConnection, "SELECT * FROM " + SourceTable + " WHERE " + KeyField + " IS NOT NULL");
            }

            if (classInfo.FindMember(ImportIdField) == null)
            {
                Attribute[] attributes = new Attribute[] { new FieldImportAttribute(KeyField) };
                XPCustomMemberInfo newMember = classInfo.CreateMember(ImportIdField, table.Columns[KeyField].DataType, false, attributes);
            }

            InitializeExisting();

            _progress = 0;
            _count = table.Rows.Count;
            float record = 0;

            foreach (PropertyInfo importProperty in DestinationType.GetProperties().Where(prop => prop.GetCustomAttributes(typeof(FieldImportAttribute), false).Length > 0))
            {
                FieldImportAttribute fieldImport = (FieldImportAttribute)importProperty.GetCustomAttributes(typeof(FieldImportAttribute), false)[0];

                propertyList.Add(importProperty, fieldImport);
            }

            foreach (DataRow row in table.Rows)
            {
                if (ImportProgress != null) ImportProgress(this, null);

                if (_count == 0)
                    _progress = 0;
                else
                    _progress = ((record++ / _count) * 100);

                if (!XpoSession.InTransaction)
                    XpoSession.BeginTransaction();

                if (!Exists(row))
                {
                    //                    Console.WriteLine("Importing: " + row[KeyField]);

                    if (Import(row))
                        existingList.Add(AsString(row[KeyField]).ToLower());
                }

                if (XpoSession.InTransaction && ((record % 500) == 0))
                    XpoSession.CommitTransaction();
            }

            if (XpoSession.InTransaction)
                XpoSession.CommitTransaction();

            _progress = 100;
            ImportProgress(this, null);

            if (ImportComplete != null) ImportComplete(this, null);
        }
    }
}
